package org.agilelang.db.toagile;

/*
 AgileLang - A fourth generation language(http://www.agilelang.org).

 Copyright (C) 2013 Roderick Cusirramos (roderick20@hotmail.com)

 This program is free software; you can redistribute it and/or modify
 it under the terms of the GNU General Public License as published by
 the Free Software Foundation; either version 2 of the License, or
 (at your option) any later version.

 This program is distributed in the hope that it will be useful,
 but WITHOUT ANY WARRANTY; without even the implied warranty of
 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 GNU General Public License for more details.

 You should have received a copy of the GNU General Public License
 along with this program; if not, write to the Free Software
 Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
 */

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.agilelang.object.Model;
import org.agilelang.object.Project;
import org.agilelang.object.attributes.Attribute;
import org.agilelang.object.attributes.Reference;
import org.agilelang.util.Inflector;

public class ReadDBMysql {

    Project project = new Project();
    Connection con = null;

    public ReadDBMysql(String host,String db, String user, String pass) {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://"+host+"/"+db;//; + this.nameDB;
            con = DriverManager.getConnection(url, user, pass);
        } catch (Exception e) {
            System.err.println("Exception: " + e.getMessage());
        }
    }
    
    void close(){
        try {
            con.close();
        } catch (SQLException ex) {
            Logger.getLogger(ReadDBMysql.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public List<Model> getListTables() {
        DatabaseMetaData meta;
        List<Model> models = new ArrayList<Model>();
        try {
            meta = con.getMetaData();
            ResultSet res = meta.getTables(null, null, null, new String[]{"TABLE"});
            while (res.next()) {
                Model model = new Model();
                model.name = Inflector.getInstance().capitalize(Inflector.getInstance().singularize(res.getString("TABLE_NAME").toString()));
                model.table = res.getString("TABLE_NAME").toString();
                model.attributes = getAttributes(res.getString("TABLE_NAME").toString());
                model.references = getReferences(res.getString("TABLE_NAME").toString());
                models.add(model);
            }
            res.close();
        } catch (SQLException ex) {
            Logger.getLogger(ReadDBMysql.class.getName()).log(Level.SEVERE, null, ex);
        }
        return models;
    }

    List<Attribute> getAttributes(String table_name) {
        List<Attribute> attrs = new ArrayList<Attribute>();
        DatabaseMetaData meta2;
        try {
            meta2 = con.getMetaData();
            ResultSet rsColumns = meta2.getColumns(null, null, table_name, null);
            while (rsColumns.next()) {
                Attribute attr = new Attribute();
                attr.name = rsColumns.getString("COLUMN_NAME");
                attr.type = rsColumns.getString("TYPE_NAME");
                attr.length = rsColumns.getString("COLUMN_SIZE");
                attr.nullable = rsColumns.getString("NULLABLE");
                attrs.add(attr);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ReadDBMysql.class.getName()).log(Level.SEVERE, null, ex);
        }
        return attrs;
    }

    List<Reference> getReferences(String table_name) {
        List<Reference> relations = new ArrayList<Reference>();
        DatabaseMetaData meta2;
        ResultSet rs = null;
        try {
            meta2 = con.getMetaData();
            rs = meta2.getImportedKeys(con.getCatalog(), null, table_name);
            while (rs.next()) {
                Reference relation = new Reference();
                relation.foreign_key = rs.getString("FKCOLUMN_NAME");
                relation.model_reference = rs.getString("PKTABLE_NAME");
                relation.attribute_reference = rs.getString("PKCOLUMN_NAME");
                relations.add(relation);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ReadDBMysql.class.getName()).log(Level.SEVERE, null, ex);
        }

        return relations;
    }

    
}